package com.hy.dao.impl;

import com.hy.bean.ReceiveApply;
import com.hy.dao.ReceiveApplyDao;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ReceiveApplyDaoImpl implements ReceiveApplyDao {
    private QueryRunner qr = new QueryRunner();
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    @Override
    public Boolean insert(ReceiveApply receiveApply) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "insert into receive_apply values(seq_receive_apply.nextval,?,?,to_date(?,'yyyy-MM-dd'),?,?,?,to_date(?,'yyyy-MM-dd'),?)";
        int i = qr.update(conn, sql,
                receiveApply.getDeptid(),
                receiveApply.getSqr(),
                sdf.format(new Date()),
                receiveApply.getMemo(),
                "未审批",
                receiveApply.getDjr(),
                sdf.format(new Date()),
                "未提交"
        );
        DbUtils.close(conn);
        return i>0;
    }

    @Override
    public List<Map<String, Object>> select(Connection conn, String deptName, String username) throws Exception {
        String sql = "SELECT r.*,d.dept_name,sqr.username sqrname,djr.username djrname FROM " +
                " receive_apply r inner join department d on r.deptid = d.id " +
                " inner join consumer sqr on sqr.id = r.sqr " +
                " inner join consumer djr on djr.id = r.djr " +
                " where d.dept_name like ? or sqr.username like ? ";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(),"%"+deptName+"%","%"+username+"%");
        DbUtils.close(conn);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> select(Connection conn, String deptName) throws SQLException {
        String sql = "SELECT r.*,d.dept_name,sqr.username sqrname,djr.username djrname FROM " +
                " receive_apply r inner join department d on r.deptid = d.id " +
                " inner join consumer sqr on sqr.id = r.sqr " +
                " inner join consumer djr on djr.id = r.djr " +
                " where d.dept_name like ? ";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(),"%"+deptName+"%");
        DbUtils.close(conn);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> select(Connection conn, String username, int i) throws SQLException {
        String sql = "SELECT r.*,d.dept_name,sqr.username sqrname,djr.username djrname FROM " +
                " receive_apply r inner join department d on r.deptid = d.id " +
                " inner join consumer sqr on sqr.id = r.sqr " +
                " inner join consumer djr on djr.id = r.djr " +
                " where sqr.username like ? ";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(),"%"+username+"%");
        DbUtils.close(conn);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> select(Connection conn) throws SQLException {
//        SELECT * FROM receive_apply r inner join department d on r.deptid = d.id inner join consumer c on c.id = r.sqrb
        String sql = "SELECT r.*,d.dept_name,sqr.username sqrname,djr.username djrname FROM " +
                " receive_apply r inner join department d on r.deptid = d.id " +
                " inner join consumer sqr on sqr.id = r.sqr " +
                " inner join consumer djr on djr.id = r.djr ";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler());
        DbUtils.close(conn);
        return mapList;
    }

    @Override
    public Map<String, Object> selectNew() throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "SELECT r.*,d.dept_name,sqr.username sqrname,djr.username djrname FROM " +
                " receive_apply r inner join department d on r.deptid = d.id " +
                " inner join consumer c on c.id = r.sqr " +
                " inner join consumer djr on djr.id = r.djr " +
                " where r.id = (" +
                "      SELECT max(id) FROM receive_apply" +
                ")";
        Map<String, Object> map = qr.query(conn, sql, new MapHandler());
        DbUtils.close(conn);
        return map;
    }

    @Override
    public List<Map<String, Object>> selectApplyDetailByApplyId(Integer id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select * from RECEIVE_APPLY_DETAIL where lysqid = ?";
        List<Map<String, Object>> maps = qr.query(conn, sql, new MapListHandler(), id);
        DbUtils.close(conn);
        return maps;
    }

    @Override
    public Boolean deleteById(Integer id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "delete from RECEIVE_APPLY where id = ?";
        int count = qr.update(conn, sql, id);
        DbUtils.close(conn);
        return count>0;
    }

    @Override
    public Boolean update(ReceiveApply receiveApply) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "update receive_apply set deptid = ?,sqr = ?,memo = ?,djr = ?,djsj = to_date(?,'yyyy-mm-dd') where id = ?";
        int count = qr.update(conn, sql, receiveApply.getDeptid(),
                receiveApply.getSqr(),
                receiveApply.getMemo(),
                receiveApply.getDjr(),
                sdf.format(new Date()),
                receiveApply.getId()
                );
        DbUtils.close(conn);
        return count>0;
    }

    @Override
    public Boolean updateDjstatus(Integer id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "update receive_apply set djstatus = ?, status = ? where id = ?";
        int count = qr.update(conn, sql,"已提交","审批中",id);
        DbUtils.close(conn);
        return count>0;
    }
}
